package com.huaxia.dao.promotion;

import com.huaxia.pojo.promotion.BranchSBM;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/09 14:31
 */


public interface BranchSbmMapper {

    @Select("\n" +
            "select b.branchsname,b.branchNumSBM,a.branchQuarterNumSBM,round(decode(b.branchNumSBM,0,0,a.branchQuarterNumSBM*100/b.branchNumSBM),1)branchYieldSBM from\n" +
            "            (select sb.branchsname,NVL(a.num,0)branchQuarterNumSBM from\n" +
            "            (select t.branchcode,count(t.branchcode)num from D_AGENT_PROVINCE_YX2 t where t.agentgrade='SBM' and to_char(t.startdate,'q')=to_char(sysdate,'q') and t.agentstate='在职' group by t.branchcode)a right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            "        )a,\n" +
            "                (select sb.branchsname,NVL(a.num,0)branchNumSBM from\n" +
            "            (select t.branchcode,count(t.branchcode)num from D_AGENT_PROVINCE_YX2 t where t.agentgrade='SBM' and t.agentstate='在职' group by t.branchcode)a right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            "        )b where a.branchsname=b.branchsname ORDER BY a.branchQuarterNumSBM desc")
    List<BranchSBM> getBranchSBM();
}
